﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class HrmEmployeeDAL : IDALHrmEmployee
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HrmEmployeeDAL()
        {

        }

        public IList<HrmEmployeeInfo> GetHrmEmployee(string search_text)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<HrmEmployeeInfo> infoList = new List<HrmEmployeeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@search_text", search_text);

                strSQL = "SELECT em.EMP_SEQ"
                            + ", em.FIRST_NAME"
                            + ", em.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", p.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                        + " FROM HRM_PS_EMPLOYEES em"
                            + " LEFT JOIN HRM_MP_CLASSES c ON em.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS p ON em.POS_SEQ = p.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON em.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON em.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                        + " WHERE em.RECORD_STATUS <> @delete_status"
                            + " AND es.ACTIVE <> 0" // เฉพาะคนที่ active เท่านั้น
                            + " AND em.WORKING_JOB_SITE_SEQ IS NOT NULL"
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (em.FIRST_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR em.LAST_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR c.CLASS_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR p.POS_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR js.JOB_SITE_NAME LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                            + " END"
                            + " ORDER BY js.JOB_SITE_REF, p.POS_REF + 1";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        HrmEmployeeInfo infoEmployee = new HrmEmployeeInfo();
                        HrmClassInfo infoClass = new HrmClassInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmPositionInfo infoPosition = new HrmPositionInfo();
                        infoClass.Name = dr[i]["CLASS_NAME"].ToString();
                        infoJobSite.Name = dr[i]["JOB_SITE_NAME"].ToString();
                        infoPosition.Name = dr[i]["POS_NAME"].ToString();
                        infoEmployee.Seq = Convert.ToInt32(dr[i]["EMP_SEQ"]);
                        infoEmployee.Name = dr[i]["FIRST_NAME"].ToString() + " " + dr[i]["LAST_NAME"].ToString();
                        infoEmployee.InfoClass = infoClass;
                        infoEmployee.InfoJobSite = infoJobSite;
                        infoEmployee.InfoPosition = infoPosition;
                        infoList.Add(infoEmployee);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<HrmEmployeeInfo> GetHrmEmployee(string search_text,int Seqvalue)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<HrmEmployeeInfo> infoList = new List<HrmEmployeeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@search_text", search_text);
                dbManager.AddParameters(2, "@SeqValue", Seqvalue);

                strSQL = "SELECT * " +
                        " FROM (" +
                            " SELECT em.EMP_SEQ" +
                            ", em.FIRST_NAME" +
                            ", em.LAST_NAME" +
                            ", c.CLASS_NAME" +
                            ", p.POS_NAME" +
                            ", js.JOB_SITE_NAME" +
                        " FROM HRM_PS_EMPLOYEES em" +
                            " LEFT JOIN HRM_MP_CLASSES c ON em.CLASS_SEQ = c.CLASS_SEQ" +
                            " LEFT JOIN HRM_MP_POSITIONS p ON em.POS_SEQ = p.POS_SEQ" +
                            " LEFT JOIN HRM_MP_JOB_SITES js ON em.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ" +
                            " WHERE em.EMP_SEQ = @SeqValue" +
                        " UNION" +
                        " SELECT em.EMP_SEQ" +
                            ", em.FIRST_NAME" +
                            ", em.LAST_NAME" +
                            ", c.CLASS_NAME" +
                            ", p.POS_NAME" +
                            ", js.JOB_SITE_NAME" +
                        " FROM HRM_PS_EMPLOYEES em" +
                            " LEFT JOIN HRM_MP_CLASSES c ON em.CLASS_SEQ = c.CLASS_SEQ" +
                            " LEFT JOIN HRM_MP_POSITIONS p ON em.POS_SEQ = p.POS_SEQ" +
                            " LEFT JOIN HRM_MP_JOB_SITES js ON em.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ" +
                            " WHERE em.RECORD_STATUS <> @delete_status" +
                            " AND em.WORKING_JOB_SITE_SEQ IS NOT NULL" +
                            " AND CASE WHEN @search_text = '' THEN 0 = 0" +
                            " ELSE (em.FIRST_NAME LIKE CONCAT('%', @search_text , '%')" +
                            " OR em.LAST_NAME LIKE CONCAT('%', @search_text , '%')" +
                            " OR c.CLASS_NAME LIKE CONCAT('%', @search_text , '%')" +
                            " OR p.POS_NAME LIKE CONCAT('%', @search_text , '%')" +
                            " OR js.JOB_SITE_NAME LIKE CONCAT('%', @search_text , '%')) END AND em.EMP_SEQ <> @SeqValue) as t1";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        HrmEmployeeInfo infoEmployee = new HrmEmployeeInfo();
                        HrmClassInfo infoClass = new HrmClassInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmPositionInfo infoPosition = new HrmPositionInfo();
                        infoClass.Name = dr[i]["CLASS_NAME"].ToString();
                        infoJobSite.Name = dr[i]["JOB_SITE_NAME"].ToString();
                        infoPosition.Name = dr[i]["POS_NAME"].ToString();
                        infoEmployee.Seq = Convert.ToInt32(dr[i]["EMP_SEQ"]);
                        infoEmployee.Name = dr[i]["FIRST_NAME"].ToString() + " " + dr[i]["LAST_NAME"].ToString();
                        infoEmployee.InfoClass = infoClass;
                        infoEmployee.InfoJobSite = infoJobSite;
                        infoEmployee.InfoPosition = infoPosition;
                        infoList.Add(infoEmployee);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public HrmEmployeeInfo GetHrmEmployee(int emp_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            HrmEmployeeInfo info = new HrmEmployeeInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);

                strSQL = "SELECT em.EMP_SEQ"
                            + ", em.FIRST_NAME"
                            + ", em.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", p.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", em.WORKING_JOB_SITE_SEQ"
                            + ", ws.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
                        + " FROM HRM_PS_EMPLOYEES em"
                            + " LEFT JOIN HRM_MP_CLASSES c ON em.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS p ON em.POS_SEQ = p.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON p.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES ws ON em.WORKING_JOB_SITE_SEQ = ws.JOB_SITE_SEQ"
                        + " WHERE em.RECORD_STATUS <> @delete_status"
                            + " AND em.WORKING_JOB_SITE_SEQ IS NOT NULL"
                            + " AND em.EMP_SEQ = @emp_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    HrmClassInfo infoClass = new HrmClassInfo();
                    HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                    HrmPositionInfo infoPosition = new HrmPositionInfo();
                    infoClass.Name = dr[0]["CLASS_NAME"].ToString();
                    infoJobSite.Name = dr[0]["JOB_SITE_NAME"].ToString();
                    infoPosition.Name = dr[0]["POS_NAME"].ToString();
                    info.Seq = Convert.ToInt32(dr[0]["EMP_SEQ"]);
                    info.Name = dr[0]["FIRST_NAME"].ToString() + " " + dr[0]["LAST_NAME"].ToString();                    
                    info.InfoClass = infoClass;
                    info.InfoJobSite = infoJobSite;                    
                    info.InfoPosition = infoPosition;
                    info.WorkingJobSiteSeq = Convert.ToInt32(dr[0]["WORKING_JOB_SITE_SEQ"]);
                    info.WorkingJobSiteName = dr[0]["WORKING_JOB_SITE_NAME"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
    }
}
